﻿



CREATE PROCEDURE [dbo].[balcon_arhGenListaExecutieChelt]   
@idLista smallint, 
@deLaData datetime,
@panaLaData datetime,
@contStart  dbo.TipCont,
@contEnd  dbo.TipCont,
@CodUnitate smallint, 
@strFiltruNrNota varchar(2000),
@centralizat bit,
@cfm bit,
@filtruSS varchar(500),
@filtruJurnal varchar(2000),
@An varchar(4)
AS



DECLARE @ca varchar(1000)
DECLARE @ab varchar(1000)
DECLARE @al varchar(1000)
DECLARE @det varchar(1000)
DECLARE @plati varchar(1000)
DECLARE @chelt varchar(2000)
DECLARE @toate varchar(6000)
DECLARE @nAn int


SET @det =','
SET @plati =','
SET @chelt =','
SET @ca =','
SET @al =','
SET @ab =','
SET @nAn = CAST(@An as int)

SELECT @det=@det + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{CD}%' AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @ca=@ca + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{CA}%'  AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @ab=@ab + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{AB}%'  AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @al=@al + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{AL}%' AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @plati=@plati + Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '7%'  AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SELECT @chelt=@chelt +  Cont + ',' FROM arhConturiSpeciale WHERE Tipuri like'%{AA}%' AND Cont LIKE '6%'  AND [CodUnitate]=@CodUnitate AND [An]=@nAN
SET @toate = @det+@chelt+@plati+@ca+@ab+@al

PRINT @det
PRINT @ca
PRINT @ab
PRINT @al
Print @plati
Print @chelt
PRINT @toate

--DROP TABLE #TempExecChelt


	SELECT a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire+a.Denumire+a.TermDenumire Denumire,a.Anexa6,a.Terminal,a.Titlu,
		  SUM(COALESCE(Rul.CrediteAprobate,0)) CrediteAprobate, 
		  SUM(COALESCE(Rul.CreditDeschis,0)) CrediteDeschise, 
		  SUM(COALESCE(Rul.AngajamenteBugetare,0)) AngajamenteBugetare, 
		  SUM(COALESCE(Rul.AngajamenteLegale+Cnt.[DebitInitial]-Cnt.[CreditInitial],0)) AngajamenteLegale, 
		  SUM(COALESCE(Rul.PlatiNete,0)) PlatiNete,
		  SUM(COALESCE(Rul.[CreditDeschis],0)) - SUM(COALESCE(Rul.PlatiNete,0)) DisponibilCredit,
		  SUM(COALESCE(Rul.Cheltuieli,0)) Cheltuieli, 
		  SUM(COALESCE(Cnt.[DebitInitial],0)) DebitInitial,
		  SUM(COALESCE(Cnt.[CreditInitial],0)) CreditInitial
	INTO #TempExecChelt
	FROM (SELECT * FROM [arhArticoleAliniate] WITH(NOLOCK) WHERE CodUnitate=@CodUnitate AND An=@nAn) a 
	LEFT OUTER JOIN
	(SELECT --pentru soldurile initiale la angajamente legale
		  dbo.GetArticolAliniat(Cont) ArtAlCont,[CodUnitate],[An],
		  sum(DebitInitial) DebitInitial,
		  sum(CreditInitial) CreditInitial
		  FROM [arhConturi] WITH(NOLOCK)
		  WHERE [CodUnitate] = @CodUnitate AND dbo.IsContIn(','+ dbo.GetSintetic(Cont)+',',@al)<>0 
				AND (LEN(@contStart)=0 OR Cont>=@contStart)
				AND (LEN(@contEnd)=0 OR Cont<=@contEnd)
				AND Terminal = 1
				AND An=@nAn
		  GROUP BY dbo.GetArticolAliniat(Cont),[CodUnitate],[An]
	  ) CNT
	ON a.ArtAl=CNT.ArtAlCont AND a.[CodUnitate]=CNT.[CodUnitate] AND a.[An]=CNT.[An]
	LEFT OUTER JOIN 
	(SELECT  
					  dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit)) ArtAlCont, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@ca,SumaDebit)) CrediteAprobate, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@det,SumaDebit)) CreditDeschis, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@ab,SumaDebit)) AngajamenteBugetare, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@al,SumaDebit)) AngajamenteLegale, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@plati,COALESCE(SumaCredit,0)-COALESCE(SumaDebit,0))) PlatiNete, 
					  sum(dbo.IsContInRetValoare(','+dbo.GetSintetic(COALESCE(t2.ContCredit, t1.ContDebit))+',',@chelt,SumaDebit)) Cheltuieli 
				FROM 
					  (SELECT SUM(coalesce(F.ValoareLei,0)) sumaDebit, F.ContDebit 
					  FROM [arhRulajeDoc] D  INNER JOIN [arhRulaje] F ON D.[IDDocument] = F.[IDDocument] AND D.[CodUnitate] = @CodUnitate AND D.[an]=F.[An]  
					  WHERE 
					  F.ContDebit<>'N/A'  AND  dbo.IsContIn(','+ dbo.GetSintetic(F.ContDebit)+',',@toate)<>0  
					  AND D.DataDoc>=@deLaData AND D.DataDoc<=@panaLaData
					  AND (LEN(@contStart)=0  OR ContDebit>=@contStart)
					  AND (LEN(@contEnd)=0  OR ContDebit<=@contEnd)
					  AND D.[An]=@nAn
					  AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))             
					  AND (LEN(@filtruSS)=0 OR (dbo.GetSursaFinantare(F.ContDebit) IN (SELECT fart.value FROM dbo.SplitParameters(@filtruSS,',') fart)))
					  AND (LEN(LTRIM(RTRIM(@filtruJurnal)))=0 OR (IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@filtruJurnal,',') fart)))
					GROUP BY F.ContDebit) t1 

				  FULL OUTER JOIN 
					  (SELECT SUM(coalesce(F.ValoareLei,0)) sumaCredit, F.ContCredit
					  FROM [arhRulajeDoc] D WITH(NOLOCK)  INNER JOIN [arhRulaje] F  WITH(NOLOCK) ON D.[IDDocument] = F.[IDDocument] AND D.[CodUnitate] = @CodUnitate AND D.[An]=F.[An]
					  WHERE 
					  F.ContCredit <>'N/A' AND  dbo.IsContIn(','+ dbo.GetSintetic(F.ContCredit)+',',@toate)<>0  
					  AND D.DataDoc>=@deLaData AND D.DataDoc<=@panaLaData 
					  AND (LEN(@contStart)=0  OR ContCredit>=@contStart)
					  AND (LEN(@contEnd)=0  OR ContCredit<=@contEnd)
					  AND D.[An]=@nAn
					  AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
					  AND (LEN(@filtruSS)=0 OR (dbo.GetSursaFinantare(F.ContCredit) IN (SELECT fart.value FROM dbo.SplitParameters(@filtruSS,',') fart)))
					  AND (LEN(LTRIM(RTRIM(@filtruJurnal)))=0 OR (IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@filtruJurnal,',') fart)))
					  GROUP BY F.ContCredit) t2 
				ON t1.ContDebit = t2.ContCredit 
	WHERE dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit))<>'XXXXXX'            
	GROUP BY dbo.GetArticolAliniat(COALESCE(t2.ContCredit, t1.ContDebit))) RUL
	ON  rul.ArtAlCont = LTRIM(RTRIM(a.ArtAl))
	WHERE   a.ArtAl <>'N/A'  
	GROUP BY  a.ArtAl,a.AAParinte,a.Categorii,a.StartDenumire,a.Denumire,a.TermDenumire,a.Anexa6,a.Terminal,a.Titlu


UPDATE #TempExecChelt SET [CrediteAprobate] = d.ca,
[CrediteDeschise] = d.cd,
[AngajamenteBugetare] = d.ab,
[AngajamenteLegale] = d.al,
[PlatiNete] = d.pn,
[DisponibilCredit] = d.dc,
[Cheltuieli] = d.c
FROM
(SELECT AAParinte,SUM([CrediteAprobate]) ca,SUM([CrediteDeschise]) cd,SUM([AngajamenteBugetare]) ab, 
SUM([AngajamenteLegale]) al,SUM([PlatiNete]) pn,SUM([DisponibilCredit]) dc,SUM([Cheltuieli]) c
FROM #TempExecChelt a GROUP BY AAParinte) d 
WHERE d.AAParinte=#TempExecChelt.ArtAl AND [Categorii]='[TOT]'





SELECT * FROM #TempExecChelt
where ((@centralizat=0) OR ([Categorii]='[TOT]' OR Anexa6=1))  
AND (@cfm=1 OR ([CrediteAprobate] <>0 OR [AngajamenteBugetare]<>0 OR [AngajamenteLegale]<>0 OR [CrediteDeschise] <> 0 OR [PlatiNete] <> 0 OR [Cheltuieli] <> 0))
ORDER BY ArtAl


GOTO Gata
EROARE:
      RAISERROR ('Eroare la generarea balantei',16,-1)
      GOTO Gata
EROARE1:
      RAISERROR ('Va rugam completati capitolul pentru lista curenta!!',16,-1)      
      GOTO Gata
Gata: